﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using System.Data;
/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：AbilityLevelModels
*系统时间：3/25/2012 7:41:30 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 7:41:30 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class AbilityLevelDAL : IAbilityLevelDAL
    {
        private const string SQL_SELECT_ABILITYLEVEL = " select  ID,SysID,LevelID,CertificateNum,Type,UserID from AbilityLevels ";
        private const string SQL_SELECT_ABILITYLEVEL_BY_USERID = " select  top 1 ID,SysID,LevelID,CertificateNum,Type,UserID from AbilityLevels "
                                                                 + "  where UserID=@UserID";
        private const string SQL_DELETE_ABILITYLEVEL_BY_USERID = " delete from AbilityLevels "
                                                                + "  where UserID=@UserID";
        private const string SQL_UPDATE_ABILITYLEVEL_BY_USERID = " update AbilityLevels set "
                                                                    + " ID=@ID,"
                                                                    + " SysID=@SysID,"
                                                                    + " LevelID=@LevelID,"
                                                                    + " CertificateNum=@CertificateNum,"
                                                                    + " Type=@Type,"
                                                                    + " UserID=@UserID"
                                                                    + "  where UserID=@UserID";
        private const string SQL_INSERT_ABILITYLEVEL = " insert into AbilityLevels("
                                                    + " ID,SysID,LevelID,CertificateNum,Type,UserID)"
                                                    + "  values ("
                                                    + " @ID,@SysID,@LevelID,@CertificateNum,@Type,@UserID)";
        private const string FIELD_ID = "@ID";
        private const string FIELD_SysID = "@SysID";
        private const string FIELD_LevelID = "@LevelID";
        private const string FIELD_CertificateNum = "@CertificateNum";
        private const string FIELD_Type = "@Type";
        private const string FIELD_UserID = "@UserID";
        public bool InsertAbilityLevel(AbilityLevel cmodel)
        {
            bool flag = false;
            SqlParameter[] AbilityLevelParms = GetAbilityLevelParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            AbilityLevelParms[0].Value = cmodel.ID;
            AbilityLevelParms[1].Value = cmodel.SysID;
            AbilityLevelParms[2].Value = cmodel.LevelID;
            AbilityLevelParms[3].Value = cmodel.CertificateNum;
            AbilityLevelParms[4].Value = cmodel.Type;
            AbilityLevelParms[5].Value = cmodel.UserID;

            foreach (SqlParameter parm in AbilityLevelParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_ABILITYLEVEL;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool DeleteAbilityLevel(string UserID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 50);
            parm.Value = UserID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_ABILITYLEVEL_BY_USERID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool UpdateAbilityLevel(AbilityLevel cmodel)
        {
            bool flag = false;
            SqlParameter[] AbilityLevelParms = GetAbilityLevelParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            AbilityLevelParms[0].Value = cmodel.ID;
            AbilityLevelParms[1].Value = cmodel.SysID;
            AbilityLevelParms[2].Value = cmodel.LevelID;
            AbilityLevelParms[3].Value = cmodel.CertificateNum;
            AbilityLevelParms[4].Value = cmodel.Type;
            AbilityLevelParms[5].Value = cmodel.UserID;

            foreach (SqlParameter parm in AbilityLevelParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_ABILITYLEVEL_BY_USERID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public AbilityLevel SelectAbilityLevelByUserID(string UserID)
        {
            AbilityLevel cmodel = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 50);
            parm.Value = UserID;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_ABILITYLEVEL_BY_USERID, parm))
            {
                if (dr.Read())
                {
                    cmodel = new AbilityLevel();
                    if (dr.HasRows == true)
                    {
                        if (dr["ID"] != null && dr["ID"].ToString() != "")
                        {
                            cmodel.ID = int.Parse(dr["ID"].ToString());
                        }
                        if (dr["SysID"] != null && dr["SysID"].ToString() != "")
                        {
                            cmodel.SysID = dr["SysID"].ToString();
                        }
                        if (dr["LevelID"] != null && dr["LevelID"].ToString() != "")
                        {
                            cmodel.LevelID = dr["LevelID"].ToString();
                        }
                        if (dr["CertificateNum"] != null && dr["CertificateNum"].ToString() != "")
                        {
                            cmodel.CertificateNum = dr["CertificateNum"].ToString();
                        }
                        if (dr["Type"] != null && dr["Type"].ToString() != "")
                        {
                            cmodel.Type = dr["Type"].ToString();
                        }
                        if (dr["UserID"] != null && dr["UserID"].ToString() != "")
                        {
                            cmodel.UserID = dr["UserID"].ToString();
                        }
                    }
                }
            }
            return cmodel;
        }
        public List<AbilityLevel> SelectAbilityLevel()
        {
            List<AbilityLevel> selectlist = new List<AbilityLevel>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_ABILITYLEVEL))
            {
                while (dr.Read())
                {
                    AbilityLevel cmodel = new AbilityLevel();
                    //写添赋值语句
                    if (dr["ID"] != null && dr["ID"].ToString() != "")
                    {
                        cmodel.ID = int.Parse(dr["ID"].ToString());
                    }
                    if (dr["SysID"] != null && dr["SysID"].ToString() != "")
                    {
                        cmodel.SysID = dr["SysID"].ToString();
                    }
                    if (dr["LevelID"] != null && dr["LevelID"].ToString() != "")
                    {
                        cmodel.LevelID = dr["LevelID"].ToString();
                    }
                    if (dr["CertificateNum"] != null && dr["CertificateNum"].ToString() != "")
                    {
                        cmodel.CertificateNum = dr["CertificateNum"].ToString();
                    }
                    if (dr["Type"] != null && dr["Type"].ToString() != "")
                    {
                        cmodel.Type = dr["Type"].ToString();
                    }
                    if (dr["UserID"] != null && dr["UserID"].ToString() != "")
                    {
                        cmodel.UserID = dr["UserID"].ToString();
                    }

                    selectlist.Add(cmodel);
                }
            }
            return selectlist;
        }
        private SqlParameter[] GetAbilityLevelParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
	                new SqlParameter(FIELD_ID, SqlDbType.Int,4),
					new SqlParameter(FIELD_SysID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_LevelID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_CertificateNum, SqlDbType.VarChar,100),
					new SqlParameter(FIELD_Type, SqlDbType.VarChar,20),
					new SqlParameter(FIELD_UserID, SqlDbType.VarChar,50)};
            return parms;
        }

    }
}